#DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.
#Data Schema For Version 0.0.0.0

#CREATE TABLE [actor]
CREATE TABLE IF NOT EXISTS `actor` (
`actor_id` SmallInt AUTO_INCREMENT NOT NULL ,
`first_name` VarChar (45) NOT NULL ,
`last_name` VarChar (45) NOT NULL ,
PRIMARY KEY (actor_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [actor].[actor_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='actor' AND COLUMN_NAME='actor_id') THEN
ALTER TABLE `actor` ADD COLUMN `actor_id` SmallInt AUTO_INCREMENT NOT NULL ;
END IF;
#ADD COLUMN [actor].[first_name]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='actor' AND COLUMN_NAME='first_name') THEN
ALTER TABLE `actor` ADD COLUMN `first_name` VarChar (45) NOT NULL ;
END IF;
#ADD COLUMN [actor].[last_name]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='actor' AND COLUMN_NAME='last_name') THEN
ALTER TABLE `actor` ADD COLUMN `last_name` VarChar (45) NOT NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [address]
CREATE TABLE IF NOT EXISTS `address` (
`address_id` SmallInt AUTO_INCREMENT NOT NULL ,
`address` VarChar (50) NOT NULL ,
`address2` VarChar (50) NULL ,
`district` VarChar (20) NOT NULL ,
`city_id` SmallInt NOT NULL ,
`postal_code` VarChar (10) NULL ,
`phone` VarChar (20) NOT NULL ,
PRIMARY KEY (address_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [address].[address_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='address' AND COLUMN_NAME='address_id') THEN
ALTER TABLE `address` ADD COLUMN `address_id` SmallInt AUTO_INCREMENT NOT NULL ;
END IF;
#ADD COLUMN [address].[address]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='address' AND COLUMN_NAME='address') THEN
ALTER TABLE `address` ADD COLUMN `address` VarChar (50) NOT NULL ;
END IF;
#ADD COLUMN [address].[address2]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='address' AND COLUMN_NAME='address2') THEN
ALTER TABLE `address` ADD COLUMN `address2` VarChar (50) NULL ;
END IF;
#ADD COLUMN [address].[district]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='address' AND COLUMN_NAME='district') THEN
ALTER TABLE `address` ADD COLUMN `district` VarChar (20) NOT NULL ;
END IF;
#ADD COLUMN [address].[city_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='address' AND COLUMN_NAME='city_id') THEN
ALTER TABLE `address` ADD COLUMN `city_id` SmallInt NOT NULL ;
END IF;
#ADD COLUMN [address].[postal_code]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='address' AND COLUMN_NAME='postal_code') THEN
ALTER TABLE `address` ADD COLUMN `postal_code` VarChar (10) NULL ;
END IF;
#ADD COLUMN [address].[phone]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='address' AND COLUMN_NAME='phone') THEN
ALTER TABLE `address` ADD COLUMN `phone` VarChar (20) NOT NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [category]
CREATE TABLE IF NOT EXISTS `category` (
`category_id` Int AUTO_INCREMENT NOT NULL ,
`name` VarChar (25) NOT NULL ,
PRIMARY KEY (category_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [category].[category_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='category' AND COLUMN_NAME='category_id') THEN
ALTER TABLE `category` ADD COLUMN `category_id` Int AUTO_INCREMENT NOT NULL ;
END IF;
#ADD COLUMN [category].[name]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='category' AND COLUMN_NAME='name') THEN
ALTER TABLE `category` ADD COLUMN `name` VarChar (25) NOT NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [city]
CREATE TABLE IF NOT EXISTS `city` (
`city_id` SmallInt AUTO_INCREMENT NOT NULL ,
`city` VarChar (50) NOT NULL ,
`country_id` SmallInt NOT NULL ,
PRIMARY KEY (city_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [city].[city_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='city' AND COLUMN_NAME='city_id') THEN
ALTER TABLE `city` ADD COLUMN `city_id` SmallInt AUTO_INCREMENT NOT NULL ;
END IF;
#ADD COLUMN [city].[city]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='city' AND COLUMN_NAME='city') THEN
ALTER TABLE `city` ADD COLUMN `city` VarChar (50) NOT NULL ;
END IF;
#ADD COLUMN [city].[country_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='city' AND COLUMN_NAME='country_id') THEN
ALTER TABLE `city` ADD COLUMN `country_id` SmallInt NOT NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [country]
CREATE TABLE IF NOT EXISTS `country` (
`country_id` SmallInt AUTO_INCREMENT NOT NULL ,
`country` VarChar (50) NOT NULL ,
PRIMARY KEY (country_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [country].[country_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='country' AND COLUMN_NAME='country_id') THEN
ALTER TABLE `country` ADD COLUMN `country_id` SmallInt AUTO_INCREMENT NOT NULL ;
END IF;
#ADD COLUMN [country].[country]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='country' AND COLUMN_NAME='country') THEN
ALTER TABLE `country` ADD COLUMN `country` VarChar (50) NOT NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [customer]
CREATE TABLE IF NOT EXISTS `customer` (
`customer_id` SmallInt AUTO_INCREMENT NOT NULL ,
`store_id` TINYINT unsigned NOT NULL ,
`first_name` VarChar (45) NOT NULL ,
`last_name` VarChar (45) NOT NULL ,
`email` VarChar (50) NULL ,
`address_id` SmallInt NOT NULL ,
`active` TINYINT unsigned NOT NULL DEFAULT 1,
PRIMARY KEY (customer_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [customer].[customer_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='customer' AND COLUMN_NAME='customer_id') THEN
ALTER TABLE `customer` ADD COLUMN `customer_id` SmallInt AUTO_INCREMENT NOT NULL ;
END IF;
#ADD COLUMN [customer].[store_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='customer' AND COLUMN_NAME='store_id') THEN
ALTER TABLE `customer` ADD COLUMN `store_id` TINYINT unsigned NOT NULL ;
END IF;
#ADD COLUMN [customer].[first_name]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='customer' AND COLUMN_NAME='first_name') THEN
ALTER TABLE `customer` ADD COLUMN `first_name` VarChar (45) NOT NULL ;
END IF;
#ADD COLUMN [customer].[last_name]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='customer' AND COLUMN_NAME='last_name') THEN
ALTER TABLE `customer` ADD COLUMN `last_name` VarChar (45) NOT NULL ;
END IF;
#ADD COLUMN [customer].[email]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='customer' AND COLUMN_NAME='email') THEN
ALTER TABLE `customer` ADD COLUMN `email` VarChar (50) NULL ;
END IF;
#ADD COLUMN [customer].[address_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='customer' AND COLUMN_NAME='address_id') THEN
ALTER TABLE `customer` ADD COLUMN `address_id` SmallInt NOT NULL ;
END IF;
#ADD COLUMN [customer].[active]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='customer' AND COLUMN_NAME='active') THEN
ALTER TABLE `customer` ADD COLUMN `active` TINYINT unsigned NOT NULL DEFAULT 1;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [film]
CREATE TABLE IF NOT EXISTS `film` (
`film_id` SmallInt AUTO_INCREMENT NOT NULL ,
`title` VarChar (1000) NOT NULL ,
`description` Text NULL ,
`release_year` VarChar (50) NULL ,
`language_id` TINYINT unsigned NOT NULL ,
`original_language_id` TINYINT unsigned NULL ,
`rental_duration` TINYINT unsigned NOT NULL DEFAULT 3,
`rental_rate` Decimal (4, 2) NOT NULL DEFAULT 4.99,
`length` SmallInt NULL ,
`replacement_cost` Decimal (5, 2) NOT NULL DEFAULT 19.99,
`rating` VarChar (50) NULL DEFAULT 'G',
`special_features` VarChar (1000) NULL ,
PRIMARY KEY (film_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [film].[film_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film' AND COLUMN_NAME='film_id') THEN
ALTER TABLE `film` ADD COLUMN `film_id` SmallInt AUTO_INCREMENT NOT NULL ;
END IF;
#ADD COLUMN [film].[title]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film' AND COLUMN_NAME='title') THEN
ALTER TABLE `film` ADD COLUMN `title` VarChar (1000) NOT NULL ;
END IF;
#ADD COLUMN [film].[description]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film' AND COLUMN_NAME='description') THEN
ALTER TABLE `film` ADD COLUMN `description` Text NULL ;
END IF;
#ADD COLUMN [film].[release_year]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film' AND COLUMN_NAME='release_year') THEN
ALTER TABLE `film` ADD COLUMN `release_year` VarChar (50) NULL ;
END IF;
#ADD COLUMN [film].[language_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film' AND COLUMN_NAME='language_id') THEN
ALTER TABLE `film` ADD COLUMN `language_id` TINYINT unsigned NOT NULL ;
END IF;
#ADD COLUMN [film].[original_language_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film' AND COLUMN_NAME='original_language_id') THEN
ALTER TABLE `film` ADD COLUMN `original_language_id` TINYINT unsigned NULL ;
END IF;
#ADD COLUMN [film].[rental_duration]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film' AND COLUMN_NAME='rental_duration') THEN
ALTER TABLE `film` ADD COLUMN `rental_duration` TINYINT unsigned NOT NULL DEFAULT 3;
END IF;
#ADD COLUMN [film].[rental_rate]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film' AND COLUMN_NAME='rental_rate') THEN
ALTER TABLE `film` ADD COLUMN `rental_rate` Decimal (4, 2) NOT NULL DEFAULT 4.99;
END IF;
#ADD COLUMN [film].[length]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film' AND COLUMN_NAME='length') THEN
ALTER TABLE `film` ADD COLUMN `length` SmallInt NULL ;
END IF;
#ADD COLUMN [film].[replacement_cost]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film' AND COLUMN_NAME='replacement_cost') THEN
ALTER TABLE `film` ADD COLUMN `replacement_cost` Decimal (5, 2) NOT NULL DEFAULT 19.99;
END IF;
#ADD COLUMN [film].[rating]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film' AND COLUMN_NAME='rating') THEN
ALTER TABLE `film` ADD COLUMN `rating` VarChar (50) NULL DEFAULT 'G';
END IF;
#ADD COLUMN [film].[special_features]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film' AND COLUMN_NAME='special_features') THEN
ALTER TABLE `film` ADD COLUMN `special_features` VarChar (1000) NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [film_actor]
CREATE TABLE IF NOT EXISTS `film_actor` (
`actor_id` SmallInt NOT NULL ,
`film_id` SmallInt NOT NULL ,
PRIMARY KEY (actor_id,film_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [film_actor].[actor_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film_actor' AND COLUMN_NAME='actor_id') THEN
ALTER TABLE `film_actor` ADD COLUMN `actor_id` SmallInt NOT NULL ;
END IF;
#ADD COLUMN [film_actor].[film_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film_actor' AND COLUMN_NAME='film_id') THEN
ALTER TABLE `film_actor` ADD COLUMN `film_id` SmallInt NOT NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [film_category]
CREATE TABLE IF NOT EXISTS `film_category` (
`film_id` SmallInt NOT NULL ,
`category_id` Int NOT NULL ,
PRIMARY KEY (film_id,category_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [film_category].[film_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film_category' AND COLUMN_NAME='film_id') THEN
ALTER TABLE `film_category` ADD COLUMN `film_id` SmallInt NOT NULL ;
END IF;
#ADD COLUMN [film_category].[category_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film_category' AND COLUMN_NAME='category_id') THEN
ALTER TABLE `film_category` ADD COLUMN `category_id` Int NOT NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [film_text]
CREATE TABLE IF NOT EXISTS `film_text` (
`film_id` SmallInt NOT NULL ,
`title` VarChar (255) NOT NULL ,
`description` Text NULL ,
PRIMARY KEY (film_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [film_text].[film_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film_text' AND COLUMN_NAME='film_id') THEN
ALTER TABLE `film_text` ADD COLUMN `film_id` SmallInt NOT NULL ;
END IF;
#ADD COLUMN [film_text].[title]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film_text' AND COLUMN_NAME='title') THEN
ALTER TABLE `film_text` ADD COLUMN `title` VarChar (255) NOT NULL ;
END IF;
#ADD COLUMN [film_text].[description]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='film_text' AND COLUMN_NAME='description') THEN
ALTER TABLE `film_text` ADD COLUMN `description` Text NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [inventory]
CREATE TABLE IF NOT EXISTS `inventory` (
`inventory_id` Int AUTO_INCREMENT NOT NULL ,
`film_id` SmallInt NOT NULL ,
`store_id` TINYINT unsigned NOT NULL ,
PRIMARY KEY (inventory_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [inventory].[inventory_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='inventory' AND COLUMN_NAME='inventory_id') THEN
ALTER TABLE `inventory` ADD COLUMN `inventory_id` Int AUTO_INCREMENT NOT NULL ;
END IF;
#ADD COLUMN [inventory].[film_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='inventory' AND COLUMN_NAME='film_id') THEN
ALTER TABLE `inventory` ADD COLUMN `film_id` SmallInt NOT NULL ;
END IF;
#ADD COLUMN [inventory].[store_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='inventory' AND COLUMN_NAME='store_id') THEN
ALTER TABLE `inventory` ADD COLUMN `store_id` TINYINT unsigned NOT NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [language]
CREATE TABLE IF NOT EXISTS `language` (
`language_id` TINYINT unsigned NOT NULL ,
`name` Char (20) NOT NULL ,
PRIMARY KEY (language_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [language].[language_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='language' AND COLUMN_NAME='language_id') THEN
ALTER TABLE `language` ADD COLUMN `language_id` TINYINT unsigned NOT NULL ;
END IF;
#ADD COLUMN [language].[name]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='language' AND COLUMN_NAME='name') THEN
ALTER TABLE `language` ADD COLUMN `name` Char (20) NOT NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [payment]
CREATE TABLE IF NOT EXISTS `payment` (
`payment_id` SmallInt AUTO_INCREMENT NOT NULL ,
`customer_id` SmallInt NOT NULL ,
`staff_id` TINYINT unsigned NOT NULL ,
`rental_id` Int NULL ,
`amount` Decimal (5, 2) NOT NULL ,
`payment_date` DateTime NOT NULL ,
PRIMARY KEY (payment_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [payment].[payment_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='payment' AND COLUMN_NAME='payment_id') THEN
ALTER TABLE `payment` ADD COLUMN `payment_id` SmallInt AUTO_INCREMENT NOT NULL ;
END IF;
#ADD COLUMN [payment].[customer_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='payment' AND COLUMN_NAME='customer_id') THEN
ALTER TABLE `payment` ADD COLUMN `customer_id` SmallInt NOT NULL ;
END IF;
#ADD COLUMN [payment].[staff_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='payment' AND COLUMN_NAME='staff_id') THEN
ALTER TABLE `payment` ADD COLUMN `staff_id` TINYINT unsigned NOT NULL ;
END IF;
#ADD COLUMN [payment].[rental_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='payment' AND COLUMN_NAME='rental_id') THEN
ALTER TABLE `payment` ADD COLUMN `rental_id` Int NULL ;
END IF;
#ADD COLUMN [payment].[amount]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='payment' AND COLUMN_NAME='amount') THEN
ALTER TABLE `payment` ADD COLUMN `amount` Decimal (5, 2) NOT NULL ;
END IF;
#ADD COLUMN [payment].[payment_date]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='payment' AND COLUMN_NAME='payment_date') THEN
ALTER TABLE `payment` ADD COLUMN `payment_date` DateTime NOT NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [rental]
CREATE TABLE IF NOT EXISTS `rental` (
`rental_id` Int AUTO_INCREMENT NOT NULL ,
`rental_date` DateTime NOT NULL ,
`inventory_id` Int NOT NULL ,
`customer_id` SmallInt NOT NULL ,
`return_date` DateTime NULL ,
`staff_id` TINYINT unsigned NOT NULL ,
PRIMARY KEY (rental_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [rental].[rental_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='rental' AND COLUMN_NAME='rental_id') THEN
ALTER TABLE `rental` ADD COLUMN `rental_id` Int AUTO_INCREMENT NOT NULL ;
END IF;
#ADD COLUMN [rental].[rental_date]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='rental' AND COLUMN_NAME='rental_date') THEN
ALTER TABLE `rental` ADD COLUMN `rental_date` DateTime NOT NULL ;
END IF;
#ADD COLUMN [rental].[inventory_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='rental' AND COLUMN_NAME='inventory_id') THEN
ALTER TABLE `rental` ADD COLUMN `inventory_id` Int NOT NULL ;
END IF;
#ADD COLUMN [rental].[customer_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='rental' AND COLUMN_NAME='customer_id') THEN
ALTER TABLE `rental` ADD COLUMN `customer_id` SmallInt NOT NULL ;
END IF;
#ADD COLUMN [rental].[return_date]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='rental' AND COLUMN_NAME='return_date') THEN
ALTER TABLE `rental` ADD COLUMN `return_date` DateTime NULL ;
END IF;
#ADD COLUMN [rental].[staff_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='rental' AND COLUMN_NAME='staff_id') THEN
ALTER TABLE `rental` ADD COLUMN `staff_id` TINYINT unsigned NOT NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [staff]
CREATE TABLE IF NOT EXISTS `staff` (
`staff_id` TINYINT unsigned NOT NULL ,
`first_name` VarChar (45) NOT NULL ,
`last_name` VarChar (45) NOT NULL ,
`address_id` SmallInt NOT NULL ,
`picture` VarChar (50) NULL ,
`email` VarChar (50) NULL ,
`store_id` TINYINT unsigned NOT NULL ,
`active` TINYINT unsigned NOT NULL DEFAULT 1,
`username` VarChar (16) NOT NULL ,
`password` VarChar (40) NULL ,
PRIMARY KEY (staff_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [staff].[staff_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='staff' AND COLUMN_NAME='staff_id') THEN
ALTER TABLE `staff` ADD COLUMN `staff_id` TINYINT unsigned NOT NULL ;
END IF;
#ADD COLUMN [staff].[first_name]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='staff' AND COLUMN_NAME='first_name') THEN
ALTER TABLE `staff` ADD COLUMN `first_name` VarChar (45) NOT NULL ;
END IF;
#ADD COLUMN [staff].[last_name]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='staff' AND COLUMN_NAME='last_name') THEN
ALTER TABLE `staff` ADD COLUMN `last_name` VarChar (45) NOT NULL ;
END IF;
#ADD COLUMN [staff].[address_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='staff' AND COLUMN_NAME='address_id') THEN
ALTER TABLE `staff` ADD COLUMN `address_id` SmallInt NOT NULL ;
END IF;
#ADD COLUMN [staff].[picture]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='staff' AND COLUMN_NAME='picture') THEN
ALTER TABLE `staff` ADD COLUMN `picture` VarChar (50) NULL ;
END IF;
#ADD COLUMN [staff].[email]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='staff' AND COLUMN_NAME='email') THEN
ALTER TABLE `staff` ADD COLUMN `email` VarChar (50) NULL ;
END IF;
#ADD COLUMN [staff].[store_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='staff' AND COLUMN_NAME='store_id') THEN
ALTER TABLE `staff` ADD COLUMN `store_id` TINYINT unsigned NOT NULL ;
END IF;
#ADD COLUMN [staff].[active]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='staff' AND COLUMN_NAME='active') THEN
ALTER TABLE `staff` ADD COLUMN `active` TINYINT unsigned NOT NULL DEFAULT 1;
END IF;
#ADD COLUMN [staff].[username]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='staff' AND COLUMN_NAME='username') THEN
ALTER TABLE `staff` ADD COLUMN `username` VarChar (16) NOT NULL ;
END IF;
#ADD COLUMN [staff].[password]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='staff' AND COLUMN_NAME='password') THEN
ALTER TABLE `staff` ADD COLUMN `password` VarChar (40) NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#CREATE TABLE [store]
CREATE TABLE IF NOT EXISTS `store` (
`store_id` TINYINT unsigned NOT NULL ,
`manager_staff_id` TINYINT unsigned NOT NULL ,
`address_id` SmallInt NOT NULL ,
PRIMARY KEY (store_id)
)

GO

DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
#ADD COLUMN [store].[store_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='store' AND COLUMN_NAME='store_id') THEN
ALTER TABLE `store` ADD COLUMN `store_id` TINYINT unsigned NOT NULL ;
END IF;
#ADD COLUMN [store].[manager_staff_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='store' AND COLUMN_NAME='manager_staff_id') THEN
ALTER TABLE `store` ADD COLUMN `manager_staff_id` TINYINT unsigned NOT NULL ;
END IF;
#ADD COLUMN [store].[address_id]
IF NOT EXISTS(select * from information_schema.COLUMNS where TABLE_NAME='store' AND COLUMN_NAME='address_id') THEN
ALTER TABLE `store` ADD COLUMN `address_id` SmallInt NOT NULL ;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [actor]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='actor' AND table_schema = DATABASE()) THEN
ALTER TABLE `actor` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='actor' AND table_schema = DATABASE()) THEN
ALTER TABLE `actor` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [actor]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='actor' AND table_schema = DATABASE()) THEN
ALTER TABLE `actor` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='actor' AND table_schema = DATABASE()) THEN
ALTER TABLE `actor` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [actor]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='actor' AND table_schema = DATABASE()) THEN
ALTER TABLE `actor` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [address]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='address' AND table_schema = DATABASE()) THEN
ALTER TABLE `address` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='address' AND table_schema = DATABASE()) THEN
ALTER TABLE `address` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [address]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='address' AND table_schema = DATABASE()) THEN
ALTER TABLE `address` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='address' AND table_schema = DATABASE()) THEN
ALTER TABLE `address` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [address]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='address' AND table_schema = DATABASE()) THEN
ALTER TABLE `address` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [category]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='category' AND table_schema = DATABASE()) THEN
ALTER TABLE `category` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='category' AND table_schema = DATABASE()) THEN
ALTER TABLE `category` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [category]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='category' AND table_schema = DATABASE()) THEN
ALTER TABLE `category` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='category' AND table_schema = DATABASE()) THEN
ALTER TABLE `category` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [category]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='category' AND table_schema = DATABASE()) THEN
ALTER TABLE `category` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [city]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='city' AND table_schema = DATABASE()) THEN
ALTER TABLE `city` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='city' AND table_schema = DATABASE()) THEN
ALTER TABLE `city` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [city]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='city' AND table_schema = DATABASE()) THEN
ALTER TABLE `city` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='city' AND table_schema = DATABASE()) THEN
ALTER TABLE `city` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [city]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='city' AND table_schema = DATABASE()) THEN
ALTER TABLE `city` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [country]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='country' AND table_schema = DATABASE()) THEN
ALTER TABLE `country` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='country' AND table_schema = DATABASE()) THEN
ALTER TABLE `country` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [country]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='country' AND table_schema = DATABASE()) THEN
ALTER TABLE `country` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='country' AND table_schema = DATABASE()) THEN
ALTER TABLE `country` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [country]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='country' AND table_schema = DATABASE()) THEN
ALTER TABLE `country` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [customer]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='customer' AND table_schema = DATABASE()) THEN
ALTER TABLE `customer` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='customer' AND table_schema = DATABASE()) THEN
ALTER TABLE `customer` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [customer]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='customer' AND table_schema = DATABASE()) THEN
ALTER TABLE `customer` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='customer' AND table_schema = DATABASE()) THEN
ALTER TABLE `customer` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [customer]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='customer' AND table_schema = DATABASE()) THEN
ALTER TABLE `customer` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [film]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='film' AND table_schema = DATABASE()) THEN
ALTER TABLE `film` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='film' AND table_schema = DATABASE()) THEN
ALTER TABLE `film` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [film]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='film' AND table_schema = DATABASE()) THEN
ALTER TABLE `film` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='film' AND table_schema = DATABASE()) THEN
ALTER TABLE `film` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [film]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='film' AND table_schema = DATABASE()) THEN
ALTER TABLE `film` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [film_actor]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='film_actor' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_actor` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='film_actor' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_actor` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [film_actor]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='film_actor' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_actor` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='film_actor' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_actor` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [film_actor]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='film_actor' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_actor` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [film_category]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='film_category' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_category` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='film_category' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_category` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [film_category]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='film_category' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_category` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='film_category' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_category` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [film_category]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='film_category' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_category` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [film_text]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='film_text' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_text` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='film_text' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_text` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [film_text]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='film_text' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_text` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='film_text' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_text` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [film_text]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='film_text' AND table_schema = DATABASE()) THEN
ALTER TABLE `film_text` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [inventory]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='inventory' AND table_schema = DATABASE()) THEN
ALTER TABLE `inventory` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='inventory' AND table_schema = DATABASE()) THEN
ALTER TABLE `inventory` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [inventory]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='inventory' AND table_schema = DATABASE()) THEN
ALTER TABLE `inventory` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='inventory' AND table_schema = DATABASE()) THEN
ALTER TABLE `inventory` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [inventory]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='inventory' AND table_schema = DATABASE()) THEN
ALTER TABLE `inventory` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [language]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='language' AND table_schema = DATABASE()) THEN
ALTER TABLE `language` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='language' AND table_schema = DATABASE()) THEN
ALTER TABLE `language` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [language]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='language' AND table_schema = DATABASE()) THEN
ALTER TABLE `language` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='language' AND table_schema = DATABASE()) THEN
ALTER TABLE `language` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [language]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='language' AND table_schema = DATABASE()) THEN
ALTER TABLE `language` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [payment]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='payment' AND table_schema = DATABASE()) THEN
ALTER TABLE `payment` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='payment' AND table_schema = DATABASE()) THEN
ALTER TABLE `payment` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [payment]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='payment' AND table_schema = DATABASE()) THEN
ALTER TABLE `payment` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='payment' AND table_schema = DATABASE()) THEN
ALTER TABLE `payment` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [payment]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='payment' AND table_schema = DATABASE()) THEN
ALTER TABLE `payment` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [rental]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='rental' AND table_schema = DATABASE()) THEN
ALTER TABLE `rental` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='rental' AND table_schema = DATABASE()) THEN
ALTER TABLE `rental` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [rental]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='rental' AND table_schema = DATABASE()) THEN
ALTER TABLE `rental` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='rental' AND table_schema = DATABASE()) THEN
ALTER TABLE `rental` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [rental]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='rental' AND table_schema = DATABASE()) THEN
ALTER TABLE `rental` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [staff]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='staff' AND table_schema = DATABASE()) THEN
ALTER TABLE `staff` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='staff' AND table_schema = DATABASE()) THEN
ALTER TABLE `staff` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [staff]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='staff' AND table_schema = DATABASE()) THEN
ALTER TABLE `staff` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='staff' AND table_schema = DATABASE()) THEN
ALTER TABLE `staff` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [staff]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='staff' AND table_schema = DATABASE()) THEN
ALTER TABLE `staff` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#REMOVE AUDIT TRAIL CREATE FOR TABLE [store]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedBy' AND TABLE_NAME='store' AND table_schema = DATABASE()) THEN
ALTER TABLE `store` DROP `CreatedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='CreatedDate' AND TABLE_NAME='store' AND table_schema = DATABASE()) THEN
ALTER TABLE `store` DROP `CreatedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [store]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedBy' AND TABLE_NAME='store' AND table_schema = DATABASE()) THEN
ALTER TABLE `store` DROP `ModifiedBy`;
END IF;
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='ModifiedDate' AND TABLE_NAME='store' AND table_schema = DATABASE()) THEN
ALTER TABLE `store` DROP `ModifiedDate`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#REMOVE AUDIT TRAIL MODIFY FOR TABLE [store]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='Timestamp' AND TABLE_NAME='store' AND table_schema = DATABASE()) THEN
ALTER TABLE `store` DROP `Timestamp`;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

GO

#PRIMARY KEY FOR TABLE [actor]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='actor') THEN
ALTER TABLE `actor` ADD PRIMARY KEY (
	`actor_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__actor]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__actor' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__actor` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [address]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='address') THEN
ALTER TABLE `address` ADD PRIMARY KEY (
	`address_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__address]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__address' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__address` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [category]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='category') THEN
ALTER TABLE `category` ADD PRIMARY KEY (
	`category_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__category]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__category' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__category` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [city]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='city') THEN
ALTER TABLE `city` ADD PRIMARY KEY (
	`city_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__city]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__city' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__city` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [country]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='country') THEN
ALTER TABLE `country` ADD PRIMARY KEY (
	`country_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__country]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__country' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__country` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [customer]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='customer') THEN
ALTER TABLE `customer` ADD PRIMARY KEY (
	`customer_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__customer]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__customer' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__customer` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [film]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='film') THEN
ALTER TABLE `film` ADD PRIMARY KEY (
	`film_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__film]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__film' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__film` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [film_actor]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='film_actor') THEN
ALTER TABLE `film_actor` ADD PRIMARY KEY (
	`actor_id`,
	`film_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__film_actor]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__film_actor' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__film_actor` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [film_category]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='film_category') THEN
ALTER TABLE `film_category` ADD PRIMARY KEY (
	`film_id`,
	`category_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__film_category]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__film_category' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__film_category` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [film_text]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='film_text') THEN
ALTER TABLE `film_text` ADD PRIMARY KEY (
	`film_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__film_text]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__film_text' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__film_text` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [inventory]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='inventory') THEN
ALTER TABLE `inventory` ADD PRIMARY KEY (
	`inventory_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__inventory]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__inventory' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__inventory` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [language]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='language') THEN
ALTER TABLE `language` ADD PRIMARY KEY (
	`language_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__language]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__language' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__language` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [payment]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='payment') THEN
ALTER TABLE `payment` ADD PRIMARY KEY (
	`payment_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__payment]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__payment' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__payment` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [rental]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='rental') THEN
ALTER TABLE `rental` ADD PRIMARY KEY (
	`rental_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__rental]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__rental' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__rental` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [staff]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='staff') THEN
ALTER TABLE `staff` ADD PRIMARY KEY (
	`staff_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__staff]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__staff' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__staff` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#PRIMARY KEY FOR TABLE [store]
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='store') THEN
ALTER TABLE `store` ADD PRIMARY KEY (
	`store_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE TEMP_PROC;
GO

#DROP PRIMARY KEY FOR TABLE [__AUDIT__store]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF EXISTS(select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME='__AUDIT__store' AND table_schema = DATABASE()) THEN
ALTER TABLE `__AUDIT__store` DROP PRIMARY KEY;
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [city] -> [address] ([city].[city_id] -> [address].[city_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__ADDRESS_CITY') THEN
ALTER TABLE `address` ADD CONSTRAINT `FK__ADDRESS_CITY` FOREIGN KEY (
	`city_id`
) REFERENCES `city` (
	`city_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [country] -> [city] ([country].[country_id] -> [city].[country_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__CITY_COUNTRY') THEN
ALTER TABLE `city` ADD CONSTRAINT `FK__CITY_COUNTRY` FOREIGN KEY (
	`country_id`
) REFERENCES `country` (
	`country_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [address] -> [customer] ([address].[address_id] -> [customer].[address_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__CUSTOMER_ADDRESS') THEN
ALTER TABLE `customer` ADD CONSTRAINT `FK__CUSTOMER_ADDRESS` FOREIGN KEY (
	`address_id`
) REFERENCES `address` (
	`address_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [store] -> [customer] ([store].[store_id] -> [customer].[store_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__CUSTOMER_STORE') THEN
ALTER TABLE `customer` ADD CONSTRAINT `FK__CUSTOMER_STORE` FOREIGN KEY (
	`store_id`
) REFERENCES `store` (
	`store_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [language] -> [film] ([language].[language_id] -> [film].[language_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__FILM_LANGUAGE') THEN
ALTER TABLE `film` ADD CONSTRAINT `FK__FILM_LANGUAGE` FOREIGN KEY (
	`language_id`
) REFERENCES `language` (
	`language_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [language] -> [film] ([language].[language_id] -> [film].[original_language_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK_ORIGINAL_FILM_LANGUAGE') THEN
ALTER TABLE `film` ADD CONSTRAINT `FK_ORIGINAL_FILM_LANGUAGE` FOREIGN KEY (
	`original_language_id`
) REFERENCES `language` (
	`language_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [actor] -> [film_actor] ([actor].[actor_id] -> [film_actor].[actor_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__FILM_ACTOR_ACTOR') THEN
ALTER TABLE `film_actor` ADD CONSTRAINT `FK__FILM_ACTOR_ACTOR` FOREIGN KEY (
	`actor_id`
) REFERENCES `actor` (
	`actor_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [film] -> [film_actor] ([film].[film_id] -> [film_actor].[film_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__FILM_ACTOR_FILM') THEN
ALTER TABLE `film_actor` ADD CONSTRAINT `FK__FILM_ACTOR_FILM` FOREIGN KEY (
	`film_id`
) REFERENCES `film` (
	`film_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [category] -> [film_category] ([category].[category_id] -> [film_category].[category_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__FILM_CATEGORY_CATEGORY') THEN
ALTER TABLE `film_category` ADD CONSTRAINT `FK__FILM_CATEGORY_CATEGORY` FOREIGN KEY (
	`category_id`
) REFERENCES `category` (
	`category_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [film] -> [film_category] ([film].[film_id] -> [film_category].[film_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__FILM_CATEGORY_FILM') THEN
ALTER TABLE `film_category` ADD CONSTRAINT `FK__FILM_CATEGORY_FILM` FOREIGN KEY (
	`film_id`
) REFERENCES `film` (
	`film_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [film] -> [inventory] ([film].[film_id] -> [inventory].[film_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__INVENTORY_FILM') THEN
ALTER TABLE `inventory` ADD CONSTRAINT `FK__INVENTORY_FILM` FOREIGN KEY (
	`film_id`
) REFERENCES `film` (
	`film_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [store] -> [inventory] ([store].[store_id] -> [inventory].[store_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__INVENTORY_STORE') THEN
ALTER TABLE `inventory` ADD CONSTRAINT `FK__INVENTORY_STORE` FOREIGN KEY (
	`store_id`
) REFERENCES `store` (
	`store_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [customer] -> [payment] ([customer].[customer_id] -> [payment].[customer_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__PAYMENT_CUSTOMER') THEN
ALTER TABLE `payment` ADD CONSTRAINT `FK__PAYMENT_CUSTOMER` FOREIGN KEY (
	`customer_id`
) REFERENCES `customer` (
	`customer_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [rental] -> [payment] ([rental].[rental_id] -> [payment].[rental_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__PAYMENT_RENTAL') THEN
ALTER TABLE `payment` ADD CONSTRAINT `FK__PAYMENT_RENTAL` FOREIGN KEY (
	`rental_id`
) REFERENCES `rental` (
	`rental_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [staff] -> [payment] ([staff].[staff_id] -> [payment].[staff_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__PAYMENT_STAFF') THEN
ALTER TABLE `payment` ADD CONSTRAINT `FK__PAYMENT_STAFF` FOREIGN KEY (
	`staff_id`
) REFERENCES `staff` (
	`staff_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [customer] -> [rental] ([customer].[customer_id] -> [rental].[customer_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__RENTAL_CUSTOMER') THEN
ALTER TABLE `rental` ADD CONSTRAINT `FK__RENTAL_CUSTOMER` FOREIGN KEY (
	`customer_id`
) REFERENCES `customer` (
	`customer_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [inventory] -> [rental] ([inventory].[inventory_id] -> [rental].[inventory_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__RENTAL_INVENTORY') THEN
ALTER TABLE `rental` ADD CONSTRAINT `FK__RENTAL_INVENTORY` FOREIGN KEY (
	`inventory_id`
) REFERENCES `inventory` (
	`inventory_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [staff] -> [rental] ([staff].[staff_id] -> [rental].[staff_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__RENTAL_STAFF') THEN
ALTER TABLE `rental` ADD CONSTRAINT `FK__RENTAL_STAFF` FOREIGN KEY (
	`staff_id`
) REFERENCES `staff` (
	`staff_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [address] -> [staff] ([address].[address_id] -> [staff].[address_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__STAFF_ADDRESS') THEN
ALTER TABLE `staff` ADD CONSTRAINT `FK__STAFF_ADDRESS` FOREIGN KEY (
	`address_id`
) REFERENCES `address` (
	`address_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [store] -> [staff] ([store].[store_id] -> [staff].[store_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__STAFF_STORE') THEN
ALTER TABLE `staff` ADD CONSTRAINT `FK__STAFF_STORE` FOREIGN KEY (
	`store_id`
) REFERENCES `store` (
	`store_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [address] -> [store] ([address].[address_id] -> [store].[address_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK__STORE_ADDRESS') THEN
ALTER TABLE `store` ADD CONSTRAINT `FK__STORE_ADDRESS` FOREIGN KEY (
	`address_id`
) REFERENCES `address` (
	`address_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#FOREIGN KEY RELATIONSHIP [staff] -> [store] ([staff].[staff_id] -> [store].[manager_staff_id])
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME='FK_MANAGER_STORE_STAFF') THEN
ALTER TABLE `store` ADD CONSTRAINT `FK_MANAGER_STORE_STAFF` FOREIGN KEY (
	`manager_staff_id`
) REFERENCES `staff` (
	`staff_id`
);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [actor] COLUMNS: [last_name]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='actor' and INDEX_NAME='IDX_ACTOR_LAST_NAME') THEN
CREATE INDEX IDX_ACTOR_LAST_NAME ON `actor` (`last_name` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [address] COLUMNS: [city_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='address' and INDEX_NAME='IDX_ADDRESS_CITY_ID') THEN
CREATE INDEX IDX_ADDRESS_CITY_ID ON `address` (`city_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [city] COLUMNS: [country_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='city' and INDEX_NAME='IDX_CITY_COUNTRY_ID') THEN
CREATE INDEX IDX_CITY_COUNTRY_ID ON `city` (`country_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [customer] COLUMNS: [store_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='customer' and INDEX_NAME='IDX_CUSTOMER_STORE_ID') THEN
CREATE INDEX IDX_CUSTOMER_STORE_ID ON `customer` (`store_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [customer] COLUMNS: [address_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='customer' and INDEX_NAME='IDX_CUSTOMER_ADDRESS_ID') THEN
CREATE INDEX IDX_CUSTOMER_ADDRESS_ID ON `customer` (`address_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [customer] COLUMNS: [last_name]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='customer' and INDEX_NAME='IDX_CUSTOMER_LAST_NAME') THEN
CREATE INDEX IDX_CUSTOMER_LAST_NAME ON `customer` (`last_name` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [film] COLUMNS: [title]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='film' and INDEX_NAME='IDX_FILM_TITLE') THEN
CREATE INDEX IDX_FILM_TITLE ON `film` (`title` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [film] COLUMNS: [language_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='film' and INDEX_NAME='IDX_FILM_LANGUAGE_ID') THEN
CREATE INDEX IDX_FILM_LANGUAGE_ID ON `film` (`language_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [film] COLUMNS: [original_language_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='film' and INDEX_NAME='IDX_FILM_ORIGINAL_LANGUAGE_ID') THEN
CREATE INDEX IDX_FILM_ORIGINAL_LANGUAGE_ID ON `film` (`original_language_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [film_actor] COLUMNS: [film_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='film_actor' and INDEX_NAME='IDX_FILM_ACTOR_FILM_ID') THEN
CREATE INDEX IDX_FILM_ACTOR_FILM_ID ON `film_actor` (`film_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [film_category] COLUMNS: [category_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='film_category' and INDEX_NAME='IDX_FILM_CATEGORY_CATEGORY_ID') THEN
CREATE INDEX IDX_FILM_CATEGORY_CATEGORY_ID ON `film_category` (`category_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [inventory] COLUMNS: [film_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='inventory' and INDEX_NAME='IDX_INVENTORY_FILM_ID') THEN
CREATE INDEX IDX_INVENTORY_FILM_ID ON `inventory` (`film_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [inventory] COLUMNS: [store_id],  [film_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='inventory' and INDEX_NAME='IDX_INVENTORY_STORE_ID_FILM_ID') THEN
CREATE INDEX IDX_INVENTORY_STORE_ID_FILM_ID ON `inventory` (`store_id` ASC,`film_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [payment] COLUMNS: [staff_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='payment' and INDEX_NAME='IDX_PAYMENT_STAFF_ID') THEN
CREATE INDEX IDX_PAYMENT_STAFF_ID ON `payment` (`staff_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [payment] COLUMNS: [customer_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='payment' and INDEX_NAME='IDX_PAYMENT_CUSTOMER_ID') THEN
CREATE INDEX IDX_PAYMENT_CUSTOMER_ID ON `payment` (`customer_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [payment] COLUMNS: [rental_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='payment' and INDEX_NAME='IDX_PAYMENT_RENTAL_ID') THEN
CREATE INDEX IDX_PAYMENT_RENTAL_ID ON `payment` (`rental_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [rental] COLUMNS: [rental_date],  [inventory_id],  [customer_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='rental' and INDEX_NAME='IDX_RENTAL_RENTAL_DATE_INVENTORY_ID_CUSTOMER_ID') THEN
CREATE UNIQUE INDEX IDX_RENTAL_RENTAL_DATE_INVENTORY_ID_CUSTOMER_ID ON `rental` (`rental_date` ASC,`inventory_id` ASC,`customer_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [rental] COLUMNS: [inventory_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='rental' and INDEX_NAME='IDX_RENTAL_INVENTORY_ID') THEN
CREATE INDEX IDX_RENTAL_INVENTORY_ID ON `rental` (`inventory_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [rental] COLUMNS: [customer_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='rental' and INDEX_NAME='IDX_RENTAL_CUSTOMER_ID') THEN
CREATE INDEX IDX_RENTAL_CUSTOMER_ID ON `rental` (`customer_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [rental] COLUMNS: [staff_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='rental' and INDEX_NAME='IDX_RENTAL_STAFF_ID') THEN
CREATE INDEX IDX_RENTAL_STAFF_ID ON `rental` (`staff_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [staff] COLUMNS: [store_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='staff' and INDEX_NAME='IDX_STAFF_STORE_ID') THEN
CREATE INDEX IDX_STAFF_STORE_ID ON `staff` (`store_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [staff] COLUMNS: [address_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='staff' and INDEX_NAME='IDX_STAFF_ADDRESS_ID') THEN
CREATE INDEX IDX_STAFF_ADDRESS_ID ON `staff` (`address_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [store] COLUMNS: [manager_staff_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='store' and INDEX_NAME='IDX_STORE_MANAGER_STAFF_ID') THEN
CREATE UNIQUE INDEX IDX_STORE_MANAGER_STAFF_ID ON `store` (`manager_staff_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#INDEX FOR TABLE [store] COLUMNS: [address_id]
DROP PROCEDURE IF EXISTS `TEMP_PROC`
GO
CREATE PROCEDURE TEMP_PROC()
BEGIN
IF NOT EXISTS(select * from information_schema.statistics where TABLE_NAME='store' and INDEX_NAME='IDX_STORE_ADDRESS_ID') THEN
CREATE INDEX IDX_STORE_ADDRESS_ID ON `store` (`address_id` ASC);
END IF;
END
GO
call TEMP_PROC();
GO
DROP PROCEDURE IF EXISTS `TEMP_PROC`;
GO

#BEGIN DEFAULTS FOR TABLE [customer]
#ADD DEFAULT CONSTRAINT FOR '[customer].[active]'
ALTER TABLE `customer` ALTER COLUMN `active` SET DEFAULT 1;

#END DEFAULTS FOR TABLE [customer]
GO

#BEGIN DEFAULTS FOR TABLE [film]
#ADD DEFAULT CONSTRAINT FOR '[film].[rating]'
ALTER TABLE `film` ALTER COLUMN `rating` SET DEFAULT 'G';

#ADD DEFAULT CONSTRAINT FOR '[film].[rental_duration]'
ALTER TABLE `film` ALTER COLUMN `rental_duration` SET DEFAULT 3;

#ADD DEFAULT CONSTRAINT FOR '[film].[rental_rate]'
ALTER TABLE `film` ALTER COLUMN `rental_rate` SET DEFAULT 4.99;

#ADD DEFAULT CONSTRAINT FOR '[film].[replacement_cost]'
ALTER TABLE `film` ALTER COLUMN `replacement_cost` SET DEFAULT 19.99;

#END DEFAULTS FOR TABLE [film]
GO

#BEGIN DEFAULTS FOR TABLE [staff]
#ADD DEFAULT CONSTRAINT FOR '[staff].[active]'
ALTER TABLE `staff` ALTER COLUMN `active` SET DEFAULT 1;

#END DEFAULTS FOR TABLE [staff]
GO

